﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;
using System.Windows.Forms;

namespace TMS
{
    class Stocktaking
    {
        public Int32 ID;
        public DateTime Date;
        public Int32 StocktakingListID;

        public DataSet GetAllStocktakingsFromDB()
        {
            DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();
            string sqlSelectAll = "SELECT * from stocktaking";
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            MyDA.Fill(table);
            mysqlCon.Close();
            return table;
        }

        public DataSet GetAllStocktakingLists()
        {
            DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();
            string sqlSelectAll = "SELECT * from stocktaking_lists WHERE stock_taking_ID='"+this.ID+"'";
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            MyDA.Fill(table);
            mysqlCon.Close();
            return table;
        }

        public void DeleteAllStocktakigProductsForStotakingList()
        {
            DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();
            string sql= "DELETE FROM stocktaking_products WHERE Stocktaking_list_ID='" + this.StocktakingListID + "'";
            MySqlCommand cmd = new MySqlCommand(sql, mysqlCon);
            cmd.ExecuteNonQuery();
            mysqlCon.Close();
         }

        public void DeleteStockingList()
        {
            DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();
            string sql= "SELECT * FROM stocktaking_products WHERE Stocktaking_list_ID='" + this.StocktakingListID + "'";
            MyDA.SelectCommand = new MySqlCommand(sql, mysqlCon);
            MyDA.Fill(table);
            if (table.Tables[0].Rows.Count != 0)
                MessageBox.Show("Не може да избришете листа која содржи податоци");
            else
            {
                sql = "DELETE FROM stocktaking_lists WHERE ID='" + this.StocktakingListID + "'";
                MySqlCommand cmd = new MySqlCommand(sql, mysqlCon);
                cmd.ExecuteNonQuery();
            }
            mysqlCon.Close();
        }

        public void AddStockingList(string Descriptin, string Resposnible)
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();
            string sql = "INSERT INTO stocktaking_lists VALUES (DEFAULT,'" +
                                        this.ID + "','" +
                                        this.Date.ToString("yyyy-MM-dd") + "','" +
                                        Descriptin + "','" +
                                        Resposnible + "')";
            MySqlCommand cmd = new MySqlCommand(sql, mysqlCon);
            cmd.ExecuteNonQuery();
            mysqlCon.Close();
        }

        public void UpdateStocktakingList(string Descriptnion, string Responsible)
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();
            string sql = "UPDATE stocktaking_lists SET Description='" + Descriptnion + "',Responsible='" + Responsible + "' WHERE ID='" + this.StocktakingListID + "'";
            MySqlCommand cmd = new MySqlCommand(sql, mysqlCon);
            cmd.ExecuteNonQuery();
            mysqlCon.Close();
        }

        public bool GetStatus()
        {
            DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();
            string sqlSelectAll = "SELECT Status from stocktaking WHERE ID="+this.ID;
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            MyDA.Fill(table);
            mysqlCon.Close();
            if (table.Tables[0].Rows.Count > 0)
                if (table.Tables[0].Rows[0].ItemArray[0].ToString() == "затворен")
                    return false;
                else
                    return true;
            else return true;
        }

        public void AddStocktaking()
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();
            string sql = "INSERT INTO stocktaking VALUES (DEFAULT,'" +
                                        this.Date.ToString("yyyy-MM-dd") + "','отворен')";
            MySqlCommand cmd = new MySqlCommand(sql, mysqlCon);
            cmd.ExecuteNonQuery();
            mysqlCon.Close();
        }

        public void DeleteStocktacking()
        {
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();
            string sql = "DELETE FROM stocktaking WHERE ID='" + this.ID + "'";
            MySqlCommand cmd = new MySqlCommand(sql, mysqlCon);
            cmd.ExecuteNonQuery();
            mysqlCon.Close();
        }

        public void UpdateStocktacking()
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();
            string sql = "UPDATE stocktaking SET Date='" + this.Date.ToString("yyyy-MM-dd") + "' WHERE ID='" + this.ID + "'";
            MySqlCommand cmd = new MySqlCommand(sql, mysqlCon);
            cmd.ExecuteNonQuery();
            mysqlCon.Close();
        }

        public void CloseStocktaking()
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();
            string sql = "UPDATE stocktaking SET Status='затворен' WHERE ID='" + this.ID + "'";
            MySqlCommand cmd = new MySqlCommand(sql, mysqlCon);
            cmd.ExecuteNonQuery();
            mysqlCon.Close();
        }

        public void AddStocktakingReport(Int32 ProductID, Int32 CurentQuantity, Int32 TakenQuantity,Int32 Difference,  Int32 Print_No)
        {
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();
            string sql = "INSERT INTO stocktaking_reports VALUES (DEFAULT,'" +
                                        this.ID + "','"+
                                        ProductID+"','"+
                                        CurentQuantity+"','"+
                                        TakenQuantity+"','"+
                                        Difference + "','" +
                                        Print_No+"')";
            
            MySqlCommand cmd = new MySqlCommand(sql, mysqlCon);
            cmd.ExecuteNonQuery();
            mysqlCon.Close();
        }

        public DataSet GetStocktakingReport()
        {
            DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();
            string sqlSelectAll = "SELECT * from stocktaking_reports WHERE Stocktaking_ID='" + this.ID + "'";
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            MyDA.Fill(table);
            mysqlCon.Close();
            return table;
        }

        public DataSet GetAllStocktakingProducts()
        {
            DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();
            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;
            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();
            string sqlSelectAll = "SELECT * from stocktaking_products WHERE Stocktaking_ID='" + this.ID + "'";
           // MessageBox.Show(sqlSelectAll);
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            MyDA.Fill(table);
            mysqlCon.Close();
            return table;
        }

    }
    
}
